DROP TABLE P_SUIVI_ATELIER CASCADE CONSTRAINTS;
DROP TABLE P_EXPO CASCADE CONSTRAINTS;
DROP TABLE P_CRITERE CASCADE CONSTRAINTS;
DROP TABLE P_AUDITEUR CASCADE CONSTRAINTS;
DROP TABLE P_INSCRIPTION CASCADE CONSTRAINTS;
DROP TABLE P_LOCAL CASCADE CONSTRAINTS;
DROP TABLE P_ATELIER CASCADE CONSTRAINTS;
DROP TABLE P_EVALUATION CASCADE CONSTRAINTS;
DROP TABLE P_ECOLE CASCADE CONSTRAINTS;
DROP TABLE P_CATEGORIE CASCADE CONSTRAINTS;
DROP TABLE P_EXPOSANT_ARCHIVE CASCADE CONSTRAINTS;
DROP TABLE P_TYPE CASCADE CONSTRAINTS;
DROP TABLE P_INSCRIPTION_ARCHIVE CASCADE CONSTRAINTS;
DROP TABLE P_REGION CASCADE CONSTRAINTS;
DROP TABLE P_EXPOSANT CASCADE CONSTRAINTS;
DROP SEQUENCE SEQ_ATEL;
DROP SEQUENCE SEQ_CATEGORIE;
DROP SEQUENCE SEQ_CRITERE;
DROP SEQUENCE SEQ_AUDIT;
DROP SEQUENCE SEQ_SUIVI_ATEL;
DROP SEQUENCE SEQ_INS_ARCH;
DROP SEQUENCE SEQ_EXP_ARCH;

CREATE TABLE P_TYPE
(
  NOTYPE NUMBER(2, 0) NOT NULL,
  NOMTYPE VARCHAR2(10 BYTE)
, CONSTRAINT P_TYPE_TYPE_PK PRIMARY KEY
  (
    NOTYPE
  )
  ENABLE
);

CREATE TABLE P_SUIVI_ATELIER
(
  NO_SUIVI_ATEL NUMBER(4, 0),
  NO_ATEL NUMBER(4, 0),
  NO_ANCIEN_LOC VARCHAR2(10)
);

CREATE TABLE P_REGION
(
  NOREGION NUMBER(3, 0) NOT NULL,
  NOMREGION VARCHAR2(100 BYTE)
, CONSTRAINT P_REGION_PK PRIMARY KEY
  (
    NOREGION
  )
  ENABLE
);

CREATE TABLE P_LOCAL
(
  NOLOCAL VARCHAR2(10 BYTE) NOT NULL,
  CAPACITE NUMBER(3, 0)
, CONSTRAINT P_LOCAL_NUMERO_PK PRIMARY KEY
  (
    NOLOCAL
  )
  ENABLE
);

CREATE TABLE P_INSCRIPTION_ARCHIVE
(
  SEQ_INS_ARCH NUMBER,
  NOAUDITEUR NUMBER(4, 0),
  NOATEL NUMBER(4, 0),
  DATEINSCRIPTION DATE,
  MODE_PAIEMENT CHAR(1),
  NO_CARTE VARCHAR2(20),
  NO_CHEQUE NUMBER(7, 0),
  EVENEMENT VARCHAR2(20),
  DATEMODIF DATE
);

CREATE TABLE P_INSCRIPTION
(
  DATEINSCRIPTION DATE,
  CREEPAR VARCHAR2(30),
  NOAUDITEUR NUMBER,
  MODE_PAIEMENT CHAR(1 BYTE),
  NO_CARTE VARCHAR2(20 BYTE),
  NO_CHEQUE NUMBER(7, 0),
  DATE_EXPIRE DATE,
  ACQUITTER NUMBER(1, 0),
  CONFIRMATIONENVOYEE NUMBER(1, 0),
  NOATELIER NUMBER
);

CREATE TABLE P_EXPOSANT_ARCHIVE
(
  NO_EXP_ARCH NUMBER,
  NOM VARCHAR2(60),
  NOEXPOSANT NUMBER(4, 0),
  COURRIEL VARCHAR2(20),
  DATESUPPRESSION DATE
);

CREATE TABLE P_EXPOSANT
(
  NOEXPOSANT NUMBER(4, 0) NOT NULL,
  NOM VARCHAR2(60 BYTE),
  PRENOM VARCHAR2(30 BYTE),
  NOECOLE NUMBER(3, 0),
  RUE VARCHAR2(25 BYTE),
  VILLE VARCHAR2(20 BYTE),
  CODE_POSTAL VARCHAR2(6 BYTE),
  TELEPHONE NUMBER(10, 0),
  COURRIEL VARCHAR2(20 BYTE),
  CREEPAR VARCHAR2(30 BYTE),
  NOATELIER NUMBER,
  CODEETUDIANT VARCHAR2(15)
, CONSTRAINT P_EXPOSANT_NOEXPOSANT_PK PRIMARY KEY
  (
    NOEXPOSANT
  )
  ENABLE
);

CREATE TABLE P_EXPO
(
  NOEXPO NUMBER(4, 0) NOT NULL,
  DATE_DEBUT DATE,
  DATE_FIN DATE,
  NOMHOTEL VARCHAR2(30 BYTE),
  RUE VARCHAR2(25 BYTE),
  VILLE VARCHAR2(20 BYTE),
  CODE_POSTAL VARCHAR2(6 BYTE),
  TELEPHONE NUMBER(10, 0),
  COURRIEL VARCHAR2(30 BYTE),
  CREEPAR VARCHAR2(30 BYTE)
, CONSTRAINT P_EXPO_NOEXPO_PK PRIMARY KEY
  (
    NOEXPO
  )
  ENABLE
);

CREATE TABLE P_EVALUATION
(
  NOEVAL NUMBER(4, 0) NOT NULL,
  NOAUDITEUR NUMBER(4, 0),
  NOATEL NUMBER(3, 0),
  NOCRITERE NUMBER(1, 0),
  COTE NUMBER(1, 0)
, CONSTRAINT P_EVALUATION_PK PRIMARY KEY
  (
    NOEVAL
  )
  ENABLE
);

CREATE TABLE P_ECOLE
(
  NOECOLE NUMBER(4, 0) NOT NULL,
  NOMECOLE VARCHAR2(100 BYTE),
  NOREGION NUMBER(3, 0),
  TUTEUR VARCHAR2(50 BYTE),
  RUE VARCHAR2(25 BYTE),
  VILLE VARCHAR2(20 BYTE),
  CODE_POSTAL VARCHAR2(6 BYTE),
  TELEPHONE NUMBER(10, 0),
  COURRIEL VARCHAR2(25 BYTE)
, CONSTRAINT P_ECOLE_NOECOLE_PK PRIMARY KEY
  (
    NOECOLE
  )
  ENABLE
);

CREATE TABLE P_CRITERE
(
  NOCRITERE NUMBER(2, 0) NOT NULL,
  NOMCRITERE VARCHAR2(25 BYTE)
, CONSTRAINT P_CRITERE_PK PRIMARY KEY
  (
    NOCRITERE
  )
  ENABLE
);

CREATE TABLE P_CATEGORIE
(
  NOCATEGORIE NUMBER(5, 0) NOT NULL,
  NOM VARCHAR2(25 BYTE),
  DESCRIPTION VARCHAR2(40 BYTE)
, CONSTRAINT P_CATEGORIE_PK PRIMARY KEY
  (
    NOCATEGORIE
  )
  ENABLE
);

CREATE TABLE P_AUDITEUR
(
  NOAUDITEUR NUMBER(4, 0) NOT NULL,
  CANDIDATJUGE DATE,
  CODEAUDITEUR VARCHAR2(7 BYTE),
  MOTDEPASSE VARCHAR2(7 BYTE),
  NOM VARCHAR2(60 BYTE),
  PRENOM VARCHAR2(40 BYTE),
  JUGE DATE,
  RUE VARCHAR2(25 BYTE),
  VILLE VARCHAR2(20 BYTE),
  CODE_POSTAL VARCHAR2(6 BYTE),
  NOREGION NUMBER(3, 0),
  TELEPHONE VARCHAR2(13 BYTE),
  COURRIEL VARCHAR2(25 BYTE),
  STATUT VARCHAR2(1 BYTE)
, CONSTRAINT P_AUDITEUR_NO_PK PRIMARY KEY
  (
    NOAUDITEUR
  )
  ENABLE
);

CREATE TABLE P_ATELIER
(
  NOATEL NUMBER(4, 0) NOT NULL,
  NOTYPE NUMBER,
  NOCATEGORIE NUMBER,
  NOEXPO NUMBER(4, 0),
  TITRE VARCHAR2(30 BYTE),
  LANGUE CHAR(1 BYTE),
  ACETATE_ELEC NUMBER(1, 0),
  PORTABLE NUMBER(1, 0),
  DUREE NUMBER(3, 0),
  NBMAXIMUM NUMBER(3, 0),
  NOECOLE NUMBER(4, 0),
  NOLOCAL VARCHAR2(10 BYTE),
  DATEATEL DATE,
  CREEPAR VARCHAR2(30 BYTE),
  DATECREATION DATE,
  COUTETUDIANT NUMBER(5, 2),
  COUTREGULIER NUMBER(5, 2)
, CONSTRAINT P_ATELIERS_NO_PK PRIMARY KEY
  (
    NOATEL
  )
  ENABLE
);

ALTER TABLE P_SUIVI_ATELIER
ADD CONSTRAINT P_SUIVI_ATELIER_P_ATELIER_FK1 FOREIGN KEY
(
  NO_SUIVI_ATEL
)
REFERENCES P_ATELIER
(
  NOATEL
)
 ENABLE
;

ALTER TABLE P_INSCRIPTION_ARCHIVE
ADD CONSTRAINT P_INSCRIPTION_ARCHIVE_P_A_FK1 FOREIGN KEY
(
  NOAUDITEUR
)
REFERENCES P_AUDITEUR
(
  NOAUDITEUR
)
 ENABLE
;

ALTER TABLE P_INSCRIPTION_ARCHIVE
ADD CONSTRAINT P_INSCRIPTION_ARCHIVE_P_A_FK2 FOREIGN KEY
(
  NOATEL
)
REFERENCES P_ATELIER
(
  NOATEL
)
 ENABLE
;

ALTER TABLE P_INSCRIPTION
ADD CONSTRAINT P_INSCRIPTION_P_ATELIER_FK1 FOREIGN KEY
(
  NOATELIER
)
REFERENCES P_ATELIER
(
  NOATEL
)
 ENABLE
;

ALTER TABLE P_INSCRIPTION
ADD CONSTRAINT P_INSCRIPTION_P_AUDITEUR_FK1 FOREIGN KEY
(
  NOAUDITEUR
)
REFERENCES P_AUDITEUR
(
  NOAUDITEUR
)
 ENABLE
;

ALTER TABLE P_EXPOSANT_ARCHIVE
ADD CONSTRAINT P_EXPOSANT_ARCHIVE_P_EXPO_FK1 FOREIGN KEY
(
  NOEXPOSANT
)
REFERENCES P_EXPOSANT
(
  NOEXPOSANT
)
 ENABLE
;

ALTER TABLE P_EXPOSANT
ADD CONSTRAINT P_EXPOSANT_ECOLE_FK FOREIGN KEY
(
  NOECOLE
)
REFERENCES P_ECOLE
(
  NOECOLE
)
 ENABLE
;

ALTER TABLE P_EXPOSANT
ADD CONSTRAINT P_EXPOSANT_P_ATELIER_FK1 FOREIGN KEY
(
  NOATELIER
)
REFERENCES P_ATELIER
(
  NOATEL
)
 ENABLE
;

ALTER TABLE P_EVALUATION
ADD CONSTRAINT P_EVALUATION_P_ATELIER_FK1 FOREIGN KEY
(
  NOATEL
)
REFERENCES P_ATELIER
(
  NOATEL
)
 ENABLE
;

ALTER TABLE P_EVALUATION
ADD CONSTRAINT P_EVALUATION_P_AUDITEUR_FK1 FOREIGN KEY
(
  NOAUDITEUR
)
REFERENCES P_AUDITEUR
(
  NOAUDITEUR
)
 ENABLE
;

ALTER TABLE P_EVALUATION
ADD CONSTRAINT P_EVALUATION_P_CRITERE_FK1 FOREIGN KEY
(
  NOCRITERE
)
REFERENCES P_CRITERE
(
  NOCRITERE
)
 ENABLE
;

ALTER TABLE P_ECOLE
ADD CONSTRAINT P_ECOLE_REGION_FK FOREIGN KEY
(
  NOREGION
)
REFERENCES P_REGION
(
  NOREGION
)
 ENABLE
;

ALTER TABLE P_ATELIER
ADD CONSTRAINT P_ATELIER_ECOLE_FK FOREIGN KEY
(
  NOECOLE
)
REFERENCES P_ECOLE
(
  NOECOLE
)
 ENABLE
;

ALTER TABLE P_ATELIER
ADD CONSTRAINT P_ATELIER_EXPO_FK FOREIGN KEY
(
  NOEXPO
)
REFERENCES P_EXPO
(
  NOEXPO
)
 ENABLE
;

ALTER TABLE P_ATELIER
ADD CONSTRAINT P_ATELIER_LOCAL_FK FOREIGN KEY
(
  NOLOCAL
)
REFERENCES P_LOCAL
(
  NOLOCAL
)
 ENABLE
;

ALTER TABLE P_ATELIER
ADD CONSTRAINT P_ATELIER_P_CATEGORIE_FK1 FOREIGN KEY
(
  NOCATEGORIE
)
REFERENCES P_CATEGORIE
(
  NOCATEGORIE
)
 ENABLE
;

ALTER TABLE P_ATELIER
ADD CONSTRAINT P_ATELIER_P_TYPE_FK1 FOREIGN KEY
(
  NOTYPE
)
REFERENCES P_TYPE
(
  NOTYPE
)
 ENABLE
;

ALTER TABLE P_AUDITEUR
ADD CONSTRAINT P_AUDITEUR_STATUT_CHK CHECK
(STATUT = 'R' OR STATUT = 'E')
 ENABLE
;

ALTER TABLE P_ATELIER
ADD CONSTRAINT P_ATELIER_ACETATE_ELEC_CHK CHECK
(ACETATE_ELEC = 0 OR ACETATE_ELEC = 1)
 ENABLE
;

ALTER TABLE P_ATELIER
ADD CONSTRAINT P_ATELIER_DUREE_CHK CHECK
(DUREE IN (30, 45, 60, 90))
 ENABLE
;

ALTER TABLE P_ATELIER
ADD CONSTRAINT P_ATELIER_LANGUE_CHK CHECK
(LANGUE = 'F' OR LANGUE = 'A')
 ENABLE
;

ALTER TABLE P_ATELIER
ADD CONSTRAINT P_ATELIER_PORTABLE_CHK CHECK
(PORTABLE = 0 OR PORTABLE = 1)
 ENABLE
;

CREATE SEQUENCE SEQ_SUIVI_ATEL;

CREATE SEQUENCE SEQ_EXP_ARCH;

CREATE SEQUENCE SEQ_CRITERE INCREMENT BY 1 START WITH 10;

CREATE SEQUENCE SEQ_CATEGORIE INCREMENT BY 1 START WITH 10;

CREATE SEQUENCE SEQ_AUDIT INCREMENT BY 1 START WITH 10;

CREATE SEQUENCE SEQ_ATEL INCREMENT BY 1 START WITH 10;

CREATE SEQUENCE SEQ_INS_ARCH;

CREATE
TRIGGER TRG_SUIVI_ATELIER
BEFORE INSERT ON P_SUIVI_ATELIER
FOR EACH ROW 
BEGIN
  IF :NEW.NO_SUIVI_ATEL IS NULL THEN
    SELECT SEQ_SUIVI_ATEL.NEXTVAL INTO :NEW.NO_SUIVI_ATEL FROM DUAL;
  END IF;
END;
/

CREATE
TRIGGER TRG_INSCRIPTION_ARCHIVE
BEFORE INSERT ON P_INSCRIPTION_ARCHIVE
FOR EACH ROW 
BEGIN
  IF :NEW.SEQ_INS_ARCH IS NULL THEN
    SELECT SEQ_INS_ARCH.NEXTVAL INTO :NEW.SEQ_INS_ARCH FROM DUAL;
  END IF;
END;
/

CREATE
TRIGGER TRG_EXPOSANT_ARCHIVE
BEFORE INSERT ON P_EXPOSANT_ARCHIVE
FOR EACH ROW 
BEGIN
  IF :NEW.NO_EXP_ARCH IS NULL THEN
    SELECT SEQ_EXP_ARCH.NEXTVAL INTO :NEW.NO_EXP_ARCH FROM DUAL;
  END IF;
END;
/

CREATE
TRIGGER TRG_CRITERE
BEFORE INSERT ON P_CRITERE
FOR EACH ROW 
BEGIN
  IF :NEW.NOCRITERE IS NULL THEN
    SELECT SEQ_CRITERE.NEXTVAL INTO :NEW.NOCRITERE FROM DUAL;
  END IF;
END;
/

CREATE
TRIGGER TRG_AUDITEUR
BEFORE INSERT ON P_AUDITEUR
FOR EACH ROW 
BEGIN
  IF :NEW.NOAUDITEUR IS NULL THEN
    SELECT SEQ_AUDIT.NEXTVAL INTO :NEW.NOAUDITEUR FROM DUAL;
  END IF;
END;
/

CREATE
TRIGGER TRG_ATELIER
BEFORE INSERT ON P_ATELIER
FOR EACH ROW 
BEGIN
  IF :NEW.NOATEL IS NULL THEN
    SELECT SEQ_ATEL.NEXTVAL INTO :NEW.NOATEL FROM DUAL;
  END IF;
END;
/

CREATE OR REPLACE
TRIGGER P_SUIVI_LOCAL_ATELIER
BEFORE UPDATE ON P_ATELIER
FOR EACH ROW 
BEGIN
  INSERT INTO P_SUIVI_ATELIER VALUES(SEQ_SUIVI_ATEL.NEXTVAL, :OLD.NOATEL, :OLD.NOLOCAL);
END;
/

CREATE OR REPLACE
TRIGGER TRG_SUIVI_INSCRIPTION
BEFORE INSERT OR DELETE OR UPDATE ON P_INSCRIPTION
FOR EACH ROW 
DECLARE
  vEvent varchar2(16) := 'UNKNOWN';
BEGIN
  IF INSERTING THEN
    vEvent := 'INSERTED';
  ELSIF UPDATING THEN
    vEvent := 'UPDATED';
  ELSIF DELETING THEN
    vEvent := 'DELETED';
  END IF;
  INSERT INTO P_INSCRIPTION_ARCHIVE VALUES (SEQ_INS_ARCH.NEXTVAL, :OLD.NOAUDITEUR, :OLD.NOATELIER, :OLD.DATEINSCRIPTION, :OLD.MODE_PAIEMENT, :OLD.NO_CARTE, :OLD.NO_CHEQUE, vEvent, sysdate);
END;
/

CREATE OR REPLACE
TRIGGER TRG_SUIVI_EXPOSANT
BEFORE DELETE ON P_EXPOSANT
FOR EACH ROW
BEGIN
  INSERT INTO P_EXPOSANT_ARCHIVE VALUES (SEQ_EXP_ARCH.NEXTVAL, :OLD.NOM, :OLD.NOEXPOSANT, :OLD.COURRIEL, sysdate);
END;
/

CREATE OR REPLACE
TRIGGER TRG_CODE_EXPOSANT
BEFORE INSERT ON P_EXPOSANT
FOR EACH ROW
DECLARE
  vCode varchar2(16) := 'UNKNOWN';
BEGIN
    vCode := RPAD(:NEW.NOM, 4, 'X') || RPAD(:NEW.PRENOM, 4, 'X');
    SELECT vCode || (COUNT(*)+1) INTO vCode FROM P_EXPOSANT WHERE SUBSTR(CODEETUDIANT, 0, 8) = vCode;
    SELECT vCode INTO :NEW.CODEETUDIANT FROM DUAL;
END;
/